﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Office.Interop;
using System.IO;

namespace MesDock
{
    class ExcelOperate
    {
        private Microsoft.Office.Interop.Excel.Workbook workBook;

        private Microsoft.Office.Interop.Excel.Workbook GetWorkBook(string filePath){
            try
            {
                Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlsApp == null)
                {
                    return null;
                }
                workBook = xlsApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            }
            catch (Exception)
            {
                
                throw;
            }

            return workBook;
        }
        // 摘要: 
        //     此方法用于将数据库表导出为Excel文件。
        //
        // 参数: 
        //   dataTable:
        //     要导出的数据库。
        //
        // 返回结果: 
        //   true：
        //     导出成功
        //   false:
        //     导出失败
        public bool DataExport(DataTable dataTable,string filePath)
        {
            int rowIndex = 1;
            int colIndex = 0;
            //string filePath = "D:\\设备数据报表\\";

            Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {
                return false;
            }
            //设置后台执行为不可见，值为true会先打开Excel文件，再往Excel文件里写数据。
            //xlsApp.Visible = true;
            //xlsApp.Workbooks.Add(true或者Type.Missing)：新建一个Excel表
            Microsoft.Office.Interop.Excel.Workbook workBook = xlsApp.Workbooks.Add(true);
            try
            {
                //读取的table的字段名称，设为第一行的值
                foreach (DataColumn col in dataTable.Columns)
                {
                    colIndex++;
                    xlsApp.Cells[1, colIndex] = col.ColumnName;
                }
                //填充数据
                foreach (DataRow row in dataTable.Rows)
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in dataTable.Columns)
                    {
                        colIndex++;
                        xlsApp.Cells[rowIndex, colIndex] = row[col.ColumnName];
                    }
                }


                //DateTime timeN = DateTime.Now;
                //string directory = filePath + timeN.ToLongDateString();
                //string delDir = filePath + timeN.AddMonths(-3).ToLongDateString();
                ////生成文件夹
                //if (!Directory.Exists(directory))
                //{
                //    Directory.CreateDirectory(directory);
                //}
                ////删除文件夹。第二个参数选true，表示删除该目录的子目录及子文件；如果选false，则该目录不为空时不能删除
                //if (Directory.Exists(delDir))
                //{
                //    Directory.Delete(delDir, true);
                //}

                //保存Excel表格文件
                //workBook.SaveAs(directory + "\\设备生产参数" + timeN.ToString("yyyyMMddHHmmss") + ".xlsx", Type.Missing, "", "", Type.Missing,
                //    Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing,
                //    Type.Missing, Type.Missing);
                workBook.SaveAs(filePath, Type.Missing, "", "", Type.Missing,
                    Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing,
                    Type.Missing, Type.Missing);
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                workBook.Close(false, Type.Missing, Type.Missing);
                workBook = null;
                xlsApp.Quit();
                xlsApp = null;
                GC.Collect();
            }
            return true;
        }
        /// <summary>
        /// 将数据写入Excel文件，以作为缓存文件
        /// </summary>
        /// <param name="type"></param>
        /// <param name="dataTable"></param>
        public void WriteExcel(string type, System.Data.DataTable dataTable,string filePath)
        {
            //string filePath = "D:\\设备数据\\DATA_BUFFER.xlsx";

            Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {

            }
            //值为true会启动Excel，值为false后台执行。默认值为false。
            //xlsApp.Visible = true;
            //xlsApp.Workbooks.Add(true或者Type.Missing)：新建一个Excel表
            Microsoft.Office.Interop.Excel.Workbook workBook;
            if (File.Exists(filePath))
            {
                workBook = xlsApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            }
            else
            {
                workBook = xlsApp.Workbooks.Add(true);
            }
            try
            {
                Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets.get_Item(1);
                int rowsCount = workSheet.UsedRange.Cells.Rows.Count;

                if (rowsCount == 1)
                {
                    xlsApp.Cells[rowsCount, 1] = "类型";
                    xlsApp.Cells[rowsCount, 2] = "小车流水号";
                    xlsApp.Cells[rowsCount, 3] = "工位";
                    xlsApp.Cells[rowsCount, 4] = "采集时间";
                }
                xlsApp.Cells[rowsCount + 1, 1] = type;
                xlsApp.Cells[rowsCount + 1, 2] = dataTable.Rows[0]["小车流水号"];
                xlsApp.Cells[rowsCount + 1, 3] = dataTable.Rows[0]["工位"];
                xlsApp.Cells[rowsCount + 1, 4] = dataTable.Rows[0]["采集时间"];

                new MainForm().PrintLog("开始保存Excel文件");
                if (File.Exists(filePath))
                {
                    new MainForm().PrintLog("保存Excel文件");
                    workBook.Save();
                }
                else
                {
                    new MainForm().PrintLog("生成文件夹");
                    //生成文件夹
                    if (!Directory.Exists("D:\\设备数据\\BAK\\"))
                    {
                        Directory.CreateDirectory("D:\\设备数据\\BAK\\");
                    }
                    new MainForm().PrintLog("另存Excel文件");

                    workBook.SaveAs(filePath,Type.Missing, "", "", Type.Missing,
                    Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 1, false, Type.Missing,
                    Type.Missing, Type.Missing);
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                workBook.Close(false, Type.Missing, Type.Missing);
                workBook = null;
                xlsApp.Quit();
                xlsApp = null;
                GC.Collect();
            }
        }
        /// <summary>
        /// 读取Excel文件
        /// </summary>
        /// <returns>返回一个DataTable</returns>
        public DataTable ReadExcel(string filePath)
        {
            //string filePath = "D:\\设备数据\\DATA_BUFFER.xlsx";
            DataTable table = new DataTable();

            Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {
                return null;
            }
            //值为true会启动Excel，值为false后台执行。默认值为false。
            //xlsApp.Visible = true;
            //xlsApp.Workbooks.Add(true或者Type.Missing)：新建一个Excel表
            Microsoft.Office.Interop.Excel.Workbook workBook;
            if (File.Exists(filePath))
            {
                workBook = xlsApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets.get_Item(1);
                    int rowsCount = workSheet.UsedRange.Cells.Rows.Count;
                    //添加列
                    table.Columns.Add("类型");
                    table.Columns.Add("小车流水号");
                    table.Columns.Add("工位");
                    table.Columns.Add("采集时间");
                    if (rowsCount >= 2)
                    {
                        for (int i = 2; i <= rowsCount; i++)
                        {
                            table.Rows.Add(xlsApp.Cells[i, 1].Value, xlsApp.Cells[i, 2].Value, xlsApp.Cells[i, 3].Value, xlsApp.Cells[i, 4].Value);
                        }
                        workSheet.UsedRange.Cells.Rows.Delete();
                    }
                    workBook.Save();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    workBook = null;
                    xlsApp.Quit();
                    xlsApp = null;
                    GC.Collect();
                }
            }
            return table;
        }
        /// <summary>
        /// 删除Excel文件中的所有数据
        /// </summary>
        public void DeleteExcelData(string filePath)
        {
            //string filePath = "D:\\设备数据\\DATA_BUFFER.xlsx";
            DataTable table = new DataTable();

            Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlsApp == null)
            {
                return;
            }
            //值为true会启动Excel，值为false后台执行。默认值为false。
            //xlsApp.Visible = true;
            //xlsApp.Workbooks.Add(true或者Type.Missing)：新建一个Excel表
            Microsoft.Office.Interop.Excel.Workbook workBook;
            if (File.Exists(filePath))
            {
                workBook = xlsApp.Workbooks.Open(filePath, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                try
                {
                    Microsoft.Office.Interop.Excel.Worksheet workSheet = workBook.Worksheets.get_Item(1);
                    int rowsCount = workSheet.UsedRange.Cells.Rows.Count;
                    if (rowsCount >= 2)
                    {
                        workSheet.UsedRange.Cells.Rows.Delete();
                    }
                    workBook.Save();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    workBook.Close(false, Type.Missing, Type.Missing);
                    workBook = null;
                    xlsApp.Quit();
                    xlsApp = null;
                    GC.Collect();
                }
            }
        }
    }
}
